Release 10.1A: OpenEdge Data Management:
Database Administration

PROUTIL IDXBUILD qualifier

Rebuilds an index, packing or consolidating index records to use disk space as efficiently as possible.

proutil db-name -C idxbuild  [ all |table [owner-name.]table-name | area area-name |     schema schema-owner | activeindexes | inactiveindexes]  [ -threads n ] [ -threadnum n ]  [ -T dir-name | -SS sort-file-directory-specification ]  [ -TB blocksize ] [ -TM n ] [ -B n ] [ -SG n ]

Parameters

db-name

Specifies the database you are using.

all

Specifies that you want to rebuild all your indexes. PROUTIL automatically rebuilds all your indexes without asking about disk space requirements.

table [owner-name.]table-name

Specifies that you want to rebuild the indexes defined for the named table. When the table option is used on the command line, PROUTIL automatically rebuilds the indexes defined for the table without asking about disk space requirements.

area area-name

Specifies that you want to rebuild all the indexes defined in the named area. When the area option is used on the command line, PROUTIL automatically rebuilds the indexes defined for the area without asking about disk space requirements.

schema schema-owner

Specifies that you want to rebuild all the indexes owned by the named schema-owner. When the schema option is used on the command line, PROUTIL automatically rebuilds the indexes owned by the schema-owner without asking about disk space requirements.

activeindexes

Specifies that you want all currently active indexes rebuilt.

inactiveindexes

Specifies that you want all currently inactive indexes rebuilt. When an inactive index is rebuilt, it becomes active.

-threads n

For Enterprise licenses, IDXBUILD is a multi-threaded operation by default. The -threads parameter allows you to turn off multi-threading. By default, n is equal to 1, indicating that the IDXBUILD is multi-threaded. Setting n to 0 turns off multi-threading.

-threadnum n

For Enterprise licenses, when IDXBUILD is executed in a multi-threaded mode, the -threadnum parameter allows you to control the maximum number of threads created. By default, the maximum number of threads, n, is equal to the number of CPUs. Setting n to 0 turns off multi-threading.

-T dir-name

Specifies the name of the directory where the temporary files are stored. If you do not use this parameter, PROUTIL places temporary files in the current working directory.

-SS sort-file-directory-specification

Identifies the location of a multi-volume sort file specification. If you use the Sort Directory Specification (-SS) parameter, PROUTIL does not use the Temporary Directory (-T) parameter.

-TB n

Specifies that the index rebuild will be performed using Speed Sort. n indicates the allocated block size, in kilobytes.

-TM n

Specifies the merge number. n indicates the number of blocks or streams to be merged during the sort process.

-B n

Specifies the number of blocks in the database buffers.

-SG n

Specifies that the index rebuild will use index grouping. n indicates the number of index groups used by IDXBUILD and must be a value between 8 and 64. The default value is 48. Note that a temporary file is created for each index group.

A large -SG value requires more memory allocation and more file handles. To determine the amount of memory (in kilobytes) needed for each index group, add 1 to the merge number (the value of -TM) and multiply the sum by the speed sort block size (the value of -TB). Memory consumption for each index group equals (-TM + 1) * -TB.

If you do not specify all, table, area, or schema, the following menu appears:

Index Rebuild Utility       =====================       Select one of the following:       All (a/A) - Rebuild all the indexes       Some (s/S) - Rebuild only some of the indexes       By Area (r/R) - Rebuild indexes in selected areas       By Schema (c/C) - Rebuild indexes by schema owners       By Table (t/T) - Rebuild indexes in selected tables       By Activation (v/V) - Rebuild selected active or inactive indexes       Quit (q/Q) - Quit, do not rebuild       Enter your selection:

Table 20–2 describes the options:

Table 20–2: PROUTIL IDXBUILD options

Option	Action
All	Prompts you to verify whether you have enough disk space for index sorting.
Some	Prompts you for the indexes you want to rebuild by first entering the table name, and then the index name. IDXBUILD then prompts you to verify whether you have enough disk space for index sorting.
By Area	Prompts you for the area containing the indexes you want to rebuild, then prompts you for the indexes in the area, then prompts you to verify whether you have enough disk space for index sorting.
By Schema	Prompts you for the schema owner of the indexes you want to rebuild, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
By Table	Prompts you for the table containing the indexes you want to rebuild, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
By Activation	Prompts you to chose active or inactive indexes, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
Quit	Quits without rebuilding any indexes.

In addition to rebuilding an index, IDXBUILD also:

Compresses index blocks to minimize space usage.
Activates deactivated indexes in the database.
Repairs corrupted indexes in the database. (Index corruption is typically signaled by error messages.)

Notes

Backup your database prior to executing IDXBUILD. Under certain conditions, if the index rebuild crashes, the only method of recovery is to restore a backup.
Use IDXFIX to repair and IDXCOMPACT to compress online.
Use the Temporary Directory (-T) startup parameter to identify or redirect temporary files created by the PROUTIL utility to a specified directory when sorting and handling space issues.
Use the Speed Sort (-TB), Merge Number (-TM), Sort Grouping (-SG) and Blocks in Database Buffers (-B) startup parameters to improve index rebuild performance.
IDXBUILD does not repair corrupted record data.
Use the following formulas to determine whether you have enough free space to sort the indexes:
If you rebuild all the indexes in your database, sorting the indexes requires free space that can be as much as 75 percent of the total database size.
If you rebuild an individual index, sorting that index requires free space that can be the size of one index entry * the number of records in the file * 3.
The Index Rebuild utility rebuilds an index or set of indexes in three phases:
The utility scans the database by area, clearing all index blocks that belong to the indexes and rebuilds and adds those blocks to the free-block list.
The utility scans the database by area and rebuilds all the index entries for every data record. If you chose to sort the index, the utility writes the index entries to the sort file. Otherwise, the utility writes the index entries to the appropriate index at this point.
If you indicated that you have enough space to sort the indexes, the utility compacts the index by sorting the index entries in the sort file into groups and entering those entries in one index at a time.
For more information about using the IDXBUILD qualifier, see Chapter 13, " Managing Performance."